﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using DigDes.DSchool.SUPS.DataAccess.Interface;

namespace DigDes.DSchool.SUPS.DataAccess.Database
{
    public class ReportLoader
    {

        private static readonly string ConnectionStringKey = "SUPSDB";

        public static ReportData GetReportRow(SqlDataReader reader)
        {
            ReportData report = new ReportData();

            report.CarNumber = (String)reader["carNumber"];

            if (reader["download"] != DBNull.Value)
                report.DownloadTime = reader["download"].ToString();
            else
               report.DownloadTime = "Нет данных";

            if (reader["upload"] != DBNull.Value)
                report.UploadTime = reader["upload"].ToString();
            else
                report.UploadTime = "Нет данных";

            if (reader["departure"] != DBNull.Value)
                report.DepartureTime = reader["departure"].ToString();
            else
                report.DepartureTime = "Нет данных";

            if (reader["arrive"] != DBNull.Value)
                report.ArriveTime = reader["arrive"].ToString();
            else
                report.ArriveTime = "Нет данных";

            return report;
        }

      

        public static List<ReportData> GetSelectedCarReports(string carNumber)
        {


            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {

                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandText = "WITH myTable AS(SELECT *, rn = ROW_NUMBER() OVER (ORDER BY carNumber,Month(operationTime),DAY(operationTime),DatePart(HOUR, operationTime),DatePart(MINUTE, operationTime))FROM SomeTable)SELECT myTable.carNumber as carNumber, upload.operationTime as upload,download.operationTime as download,departute.operationTime as departure,destination.operationTime as arrive FROM myTable LEFT OUTER JOIN myTable AS upload ON myTable.rn = upload.rn + 1 AND myTable.cargoWeight > upload.cargoWeight AND myTable.carNumber = upload.carNumber LEFT OUTER JOIN myTable as download ON myTable.rn = download.rn + 1 AND myTable.cargoWeight < download.cargoWeight AND myTable.carNumber = download.carNumber LEFT OUTER JOIN myTable as departute ON myTable.rn + 1 = departute.rn AND myTable.destinationStation <> departute.destinationStation AND myTable.carNumber = departute.carNumber AND departute.operationTime IN(SELECT MIN(operationTime) from myTable where departute.destinationStation = myTable.destinationStation AND myTable.carNumber = departute.carNumber) LEFT OUTER JOIN myTable as destination on myTable.rn +1  = destination.rn and myTable.carNumber = destination.carNumber AND myTable.destinationStation = destination.stationOfOperation WHERE ((upload.cargoWeight IS NOT NULL OR download.cargoWeight IS NOT NULL)) AND myTable.carNumber = @carNumber ORDER BY myTable.carNumber ";
                    sqlCmd.Parameters.AddWithValue("@carNumber", carNumber);

                    List<ReportData> reports = new List<ReportData>();
                    sqlConn.Open();
                    using (SqlDataReader reader = sqlCmd.ExecuteReader())
                    {

                        while (reader.Read())
                        {
                            reports.Add(GetReportRow(reader));
                        }

                        if (reader != null)
                        { reader.Close(); }
                        sqlConn.Close();

                    }
                    return reports;
                }
            }

        }

        public static List<ReportData> GetAllReports()
        {


            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {

                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandText = "WITH myTable AS(SELECT *, rn = ROW_NUMBER() OVER (ORDER BY carNumber,Month(operationTime),DAY(operationTime),DatePart(HOUR, operationTime),DatePart(MINUTE, operationTime))FROM SomeTable)SELECT myTable.carNumber as carNumber, upload.operationTime as upload,download.operationTime as download,departute.operationTime as departure,destination.operationTime as arrive FROM myTable LEFT OUTER JOIN myTable AS upload ON myTable.rn = upload.rn + 1 AND myTable.cargoWeight > upload.cargoWeight AND myTable.carNumber = upload.carNumber LEFT OUTER JOIN myTable as download ON myTable.rn = download.rn + 1 AND myTable.cargoWeight < download.cargoWeight AND myTable.carNumber = download.carNumber LEFT OUTER JOIN myTable as departute ON myTable.rn + 1 = departute.rn AND myTable.destinationStation <> departute.destinationStation AND myTable.carNumber = departute.carNumber AND departute.operationTime IN(SELECT MIN(operationTime) from myTable where departute.destinationStation = myTable.destinationStation AND myTable.carNumber = departute.carNumber) LEFT OUTER JOIN myTable as destination on myTable.rn +1  = destination.rn and myTable.carNumber = destination.carNumber AND myTable.destinationStation = destination.stationOfOperation WHERE ((upload.cargoWeight IS NOT NULL OR download.cargoWeight IS NOT NULL))ORDER BY myTable.carNumber";


                    List<ReportData> reports = new List<ReportData>();
                    sqlConn.Open();
                    using (SqlDataReader reader = sqlCmd.ExecuteReader())
                    {

                        while (reader.Read())
                        {
                            reports.Add(GetReportRow(reader));
                        }

                        if (reader != null)
                        { reader.Close(); }
                        sqlConn.Close();

                    }
                    return reports;
                }
            }

        }
    }
}

